﻿using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 工作日定义
    /// </summary>
    public class WorkCalendar
    {
        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">查询数据列表</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FWorkDate,FNote,Fdeftype ");
            strSql.Append(" FROM t_WorkCalendar ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="workDate"></param>
        /// <returns></returns>
        public DataSet GetWorkDay(string workDate) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FWorkDate,FNote,Fdeftype ");
            strSql.Append(" FROM t_WorkCalendar ");
            if (workDate.Trim() != "")
            {
                strSql.Append(" where FWorkDate='" + workDate + "'");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 更具开始时间和结束时间获取数据列表
        /// </summary>
        /// <param name="begindate">开始时间</param>
        /// <param name="enddate">结束时间</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetLeaveStatisticsList(string begindate, string enddate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT   [FEntityNo]
                                    ,[FWorkDate]
                                    ,[FNote]
                                    ,[Fdeftype]
                                    FROM [ElingOA6_Admin_DSJCJ].[dbo].[t_WorkCalendar]
                                    WHERE  DATEDIFF(DAY,FWorkDate,@begindate)<=0 AND DATEDIFF(DAY,FWorkDate,@enddate)>=0");
            SqlParameter[] parameters = {
					new SqlParameter("@begindate", SqlDbType.DateTime),
					new SqlParameter("@enddate", SqlDbType.DateTime)};
            parameters[0].Value = begindate;
            parameters[1].Value = enddate;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 工作日定义保存方法
        /// </summary>
        /// <param name="fEntityNo">公司编号</param>
        /// <param name="fWorkDate">所修改或增加的年份</param>
        /// <param name="workCalendarList">修改或增加的数据集合</param>
        public void ADDFWorkDate(string fEntityNo, string fWorkDate, List<Model.WorkCalendar> workCalendarList)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_WorkCalendar ");
            strSql.Append(" where FEntityNo=@FEntityNo and YEAR(FWorkDate)=@FWorkDate");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FWorkDate", SqlDbType.VarChar)};
            parameters[0].Value = fEntityNo;
            parameters[1].Value = fWorkDate;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            foreach (Model.WorkCalendar model in workCalendarList)
            {
                cmds = new CommandInfo();
                strSql = new StringBuilder();
                strSql.Append("insert into t_WorkCalendar(");
                strSql.Append("FEntityNo,FWorkDate,FNote,Fdeftype)");
                strSql.Append(" values (");
                strSql.Append("@FEntityNo,@FWorkDate,@FNote,@Fdeftype)");
                SqlParameter[] parameters2 = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkDate", SqlDbType.DateTime),
					new SqlParameter("@FNote", SqlDbType.VarChar,200),
					new SqlParameter("@Fdeftype", SqlDbType.Char,1)};
                parameters2[0].Value = model.FEntityNo;
                parameters2[1].Value = model.FWorkDate;
                parameters2[2].Value = model.FNote;
                parameters2[3].Value = model.Fdeftype;

                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters2;
                cmdList.Add(cmds);
            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }

    }
}
